﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;

using MySql.Data.MySqlClient;

namespace WFL.Data.MySql
{
    public class MySqlOperation
    {

        #region 静态方法

        #region ExecuteNonQuery

        public static int ExecuteNonQuery(string sql, MySqlConnection connection)
        {
            return ExecuteNonQuery(sql, CommandType.Text, connection, null, null);
        }

        public static int ExecuteNonQuery(string sql, CommandType commandType, MySqlConnection connection)
        {
            return ExecuteNonQuery(sql, commandType, connection, null, null);
        }

        public static int ExecuteNonQuery(string sql, MySqlConnection connection, MySqlTransaction transaction)
        {
            return ExecuteNonQuery(sql, CommandType.Text, connection, transaction, null);
        }

        public static int ExecuteNonQuery(string sql, CommandType commandType, MySqlConnection connection, MySqlTransaction transaction)
        {
            return ExecuteNonQuery(sql, commandType, connection, transaction, null);
        }

        public static int ExecuteNonQuery(string sql, MySqlConnection connection, MySqlParameter[] parameters)
        {
            return ExecuteNonQuery(sql, CommandType.Text, connection, null, parameters);
        }

        public static int ExecuteNonQuery(string sql, CommandType commandType, MySqlConnection connection, MySqlParameter[] parameters)
        {
            return ExecuteNonQuery(sql, commandType, connection, null, parameters);
        }

        public static int ExecuteNonQuery(string sql, MySqlConnection connection, MySqlTransaction transaction, MySqlParameter[] parameters)
        {
            return ExecuteNonQuery(sql, CommandType.Text, connection, transaction, parameters);
        }

        public static int ExecuteNonQuery(string sql, CommandType commandType, MySqlConnection connection, MySqlTransaction transaction, MySqlParameter[] parameters)
        {
            MySqlCommand comm = new MySqlCommand();
            comm.Connection = connection;
            comm.CommandText = sql;
            comm.CommandType = commandType;
            comm.Transaction = transaction;
            if (parameters != null)
            {
                comm.Parameters.AddRange(parameters);
            }
            return comm.ExecuteNonQuery();
        }

        #endregion


        #region ExecuteReader

        public static MySqlDataReader ExecuteReader(string sql, MySqlConnection connection)
        {
            return ExecuteReader(sql, CommandType.Text, connection, null, null);
        }

        public static MySqlDataReader ExecuteReader(string sql, CommandType commandType, MySqlConnection connection)
        {
            return ExecuteReader(sql, commandType, connection, null, null);
        }

        public static MySqlDataReader ExecuteReader(string sql, MySqlConnection connection, MySqlTransaction transaction)
        {
            return ExecuteReader(sql, CommandType.Text, connection, transaction, null);
        }

        public static MySqlDataReader ExecuteReader(string sql, CommandType commandType, MySqlConnection connection, MySqlTransaction transaction)
        {
            return ExecuteReader(sql, commandType, connection, transaction, null);
        }

        public static MySqlDataReader ExecuteReader(string sql, MySqlConnection connection, MySqlParameter[] parameters)
        {
            return ExecuteReader(sql, CommandType.Text, connection, null, parameters);
        }

        public static MySqlDataReader ExecuteReader(string sql, CommandType commandType, MySqlConnection connection, MySqlParameter[] parameters)
        {
            return ExecuteReader(sql, commandType, connection, null, parameters);
        }

        public static MySqlDataReader ExecuteReader(string sql, MySqlConnection connection, MySqlTransaction transaction, MySqlParameter[] parameters)
        {
            return ExecuteReader(sql, CommandType.Text, connection, transaction, parameters);
        }

        public static MySqlDataReader ExecuteReader(string sql, CommandType commandType, MySqlConnection connection, MySqlTransaction transaction, MySqlParameter[] parameters)
        {
            MySqlCommand comm = new MySqlCommand();
            comm.Connection = connection;
            comm.CommandText = sql;
            comm.CommandType = commandType;
            comm.Transaction = transaction;
            if (parameters != null)
            {
                comm.Parameters.AddRange(parameters);
            }
            return comm.ExecuteReader();
        }

        #endregion


        #region ExecuteScalar

        public static object ExecuteScalar(string sql, MySqlConnection connection)
        {
            return ExecuteScalar(sql, CommandType.Text, connection, null, null);
        }

        public static object ExecuteScalar(string sql, CommandType commandType, MySqlConnection connection)
        {
            return ExecuteScalar(sql, commandType, connection, null, null);
        }

        public static object ExecuteScalar(string sql, MySqlConnection connection, MySqlTransaction transaction)
        {
            return ExecuteScalar(sql, CommandType.Text, connection, transaction, null);
        }

        public static object ExecuteScalar(string sql, CommandType commandType, MySqlConnection connection, MySqlTransaction transaction)
        {
            return ExecuteScalar(sql, commandType, connection, transaction, null);
        }

        public static object ExecuteScalar(string sql, MySqlConnection connection, MySqlParameter[] parameters)
        {
            return ExecuteScalar(sql, CommandType.Text, connection, null, parameters);
        }

        public static object ExecuteScalar(string sql, CommandType commandType, MySqlConnection connection, MySqlParameter[] parameters)
        {
            return ExecuteScalar(sql, commandType, connection, null, parameters);
        }

        public static object ExecuteScalar(string sql, MySqlConnection connection, MySqlTransaction transaction, MySqlParameter[] parameters)
        {
            return ExecuteScalar(sql, CommandType.Text, connection, transaction, parameters);
        }

        public static object ExecuteScalar(string sql, CommandType commandType, MySqlConnection connection, MySqlTransaction transaction, MySqlParameter[] parameters)
        {
            MySqlCommand comm = new MySqlCommand();
            comm.Connection = connection;
            comm.CommandText = sql;
            comm.CommandType = commandType;
            comm.Transaction = transaction;
            if (parameters != null)
            {
                comm.Parameters.AddRange(parameters);
            }
            return comm.ExecuteScalar();
        }

        #endregion


        #region ExecuteDataSet

        public static DataSet ExecuteDataSet(string sql, MySqlConnection connection)
        {
            return ExecuteDataSet(sql, CommandType.Text, connection, null, null);
        }

        public static DataSet ExecuteDataSet(string sql, CommandType commandType, MySqlConnection connection)
        {
            return ExecuteDataSet(sql, commandType, connection, null, null);
        }

        public static DataSet ExecuteDataSet(string sql, MySqlConnection connection, MySqlTransaction transaction)
        {
            return ExecuteDataSet(sql, CommandType.Text, connection, transaction, null);
        }

        public static DataSet ExecuteDataSet(string sql, CommandType commandType, MySqlConnection connection, MySqlTransaction transaction)
        {
            return ExecuteDataSet(sql, commandType, connection, transaction, null);
        }

        public static DataSet ExecuteDataSet(string sql, MySqlConnection connection, MySqlParameter[] parameters)
        {
            return ExecuteDataSet(sql, CommandType.Text, connection, null, parameters);
        }

        public static DataSet ExecuteDataSet(string sql, CommandType commandType, MySqlConnection connection, MySqlParameter[] parameters)
        {
            return ExecuteDataSet(sql, commandType, connection, null, parameters);
        }

        public static DataSet ExecuteDataSet(string sql, MySqlConnection connection, MySqlTransaction transaction, MySqlParameter[] parameters)
        {
            return ExecuteDataSet(sql, CommandType.Text, connection, transaction, parameters);
        }

        public static DataSet ExecuteDataSet(string sql, CommandType commandType, MySqlConnection connection, MySqlTransaction transaction, MySqlParameter[] parameters)
        {
            MySqlCommand comm = new MySqlCommand();
            comm.Connection = connection;
            comm.CommandText = sql;
            comm.CommandType = commandType;
            comm.Transaction = transaction;
            if (parameters != null)
            {
                comm.Parameters.AddRange(parameters);
            }
            MySqlDataAdapter adapter = new MySqlDataAdapter(comm);
            DataSet result = new DataSet();
            adapter.Fill(result);
            return result;
        }

        #endregion

        #endregion


        public string ConnectionString
        {
            get;
            set;
        }

        public MySqlOperation()
        { 
        
        }

        public MySqlOperation(string connectionString)
        {
            ConnectionString = connectionString;
        }

        protected virtual MySqlConnection GetContextConnection()
        {
            return null;
        }

        protected virtual MySqlTransaction GetContextTransaction()
        {
            return null;
        }

        private MySqlConnection GetConnection(out bool creat)
        {
            creat = false;
            MySqlConnection connection = GetContextConnection();
            if (connection == null)
            {
                connection = new MySqlConnection(ConnectionString);
                connection.Open();
                creat = true;
            }
            return connection;
        }

        private MySqlTransaction BeginTransaction(MySqlConnection connection, IsolationLevel iso, out bool newTrans)
        {
            newTrans = false;
            MySqlTransaction transaction = GetContextTransaction();
            if (transaction != null)
            {
                if (transaction.IsolationLevel != iso)
                {
                    throw new Exception("上下文中存在不同隔离级别的事务。");
                }
            }
            else
            {
                transaction = connection.BeginTransaction(iso);
                newTrans = true;
            }
            return transaction;
        }


        #region ExecuteNonQuery

        public int ExecuteNonQuery(string sql)
        {
            return ExecuteNonQuery(sql, CommandType.Text, (MySqlParameter[])null);
        }

        public int ExecuteNonQuery(string sql, CommandType commandType)
        {
            return ExecuteNonQuery(sql, commandType, (MySqlParameter[])null);
        }

        public int ExecuteNonQuery(string sql, MySqlParameter[] parameters)
        {
            return ExecuteNonQuery(sql, CommandType.Text, parameters);
        }

        public int ExecuteNonQuery(string sql, CommandType commandType, MySqlParameter[] parameters)
        {
            bool creat = false;
            MySqlConnection connection = GetConnection(out creat);
            try
            {
                return ExecuteNonQuery(sql, commandType, connection, GetContextTransaction(), parameters);
            }
            finally
            {
                if (creat)
                {
                    connection.Close();
                }
            }
        }

        public int ExecuteNonQuery(string sql, IsolationLevel iso)
        {
            return ExecuteNonQuery(sql, CommandType.Text, null, iso);
        }

        public int ExecuteNonQuery(string sql, CommandType commandType, IsolationLevel iso)
        {
            return ExecuteNonQuery(sql, commandType, null, iso);
        }

        public int ExecuteNonQuery(string sql, MySqlParameter[] parameters, IsolationLevel iso)
        {
            return ExecuteNonQuery(sql, CommandType.Text, parameters, iso);
        }

        public int ExecuteNonQuery(string sql, CommandType commandType, MySqlParameter[] parameters, IsolationLevel iso)
        {
            bool creat = false;
            MySqlConnection connection = GetConnection(out creat);
            MySqlTransaction transaction = null;
            bool newTrans = false;
            try
            {
                transaction = BeginTransaction(connection, iso, out newTrans);
                int result = ExecuteNonQuery(sql, commandType, connection, transaction, parameters);
                if (newTrans)
                {
                    transaction.Commit();
                }
                return result;
            }
            catch
            {
                if (transaction != null
                    && newTrans)
                {
                    transaction.Rollback();
                }
                throw;
            }
            finally
            {
                if (creat)
                {
                    connection.Close();
                }
            }
        }

        #endregion


        #region ExecuteScalar

        public object ExecuteScalar(string sql)
        {
            return ExecuteScalar(sql, CommandType.Text, (MySqlParameter[])null);
        }

        public object ExecuteScalar(string sql, CommandType commandType)
        {
            return ExecuteScalar(sql, commandType, (MySqlParameter[])null);
        }

        public object ExecuteScalar(string sql, MySqlParameter[] parameters)
        {
            return ExecuteScalar(sql, CommandType.Text, parameters);
        }

        public object ExecuteScalar(string sql, CommandType commandType, MySqlParameter[] parameters)
        {
            bool creat = false;
            MySqlConnection connection = GetConnection(out creat);
            try
            {
                return ExecuteScalar(sql, commandType, connection, GetContextTransaction(), parameters);
            }
            finally
            {
                if (creat)
                {
                    connection.Close();
                }
            }
        }

        public object ExecuteScalar(string sql, IsolationLevel iso)
        {
            return ExecuteScalar(sql, CommandType.Text, null, iso);
        }

        public object ExecuteScalar(string sql, CommandType commandType, IsolationLevel iso)
        {
            return ExecuteScalar(sql, commandType, null, iso);
        }

        public object ExecuteScalar(string sql, MySqlParameter[] parameters, IsolationLevel iso)
        {
            return ExecuteScalar(sql, CommandType.Text, parameters, iso);
        }

        public object ExecuteScalar(string sql, CommandType commandType, MySqlParameter[] parameters, IsolationLevel iso)
        {
            bool creat = false;
            MySqlConnection connection = GetConnection(out creat);
            MySqlTransaction transaction = null;
            bool newTrans = false;
            try
            {
                transaction = BeginTransaction(connection, iso, out newTrans);
                object result = ExecuteScalar(sql, commandType, connection, transaction, parameters);
                if (newTrans)
                {
                    transaction.Commit();
                }
                return result;
            }
            catch
            {
                if (transaction != null
                    && newTrans)
                {
                    transaction.Rollback();
                }
                throw;
            }
            finally
            {
                if (creat)
                {
                    connection.Close();
                }
            }
        }

        #endregion


        #region ExecuteDataSet

        public DataSet ExecuteDataSet(string sql)
        {
            return ExecuteDataSet(sql, CommandType.Text, (MySqlParameter[])null);
        }

        public DataSet ExecuteDataSet(string sql, CommandType commandType)
        {
            return ExecuteDataSet(sql, commandType, (MySqlParameter[])null);
        }

        public DataSet ExecuteDataSet(string sql, MySqlParameter[] parameters)
        {
            return ExecuteDataSet(sql, CommandType.Text, parameters);
        }

        public DataSet ExecuteDataSet(string sql, CommandType commandType, MySqlParameter[] parameters)
        {
            bool creat = false;
            MySqlConnection connection = GetConnection(out creat);
            try
            {
                return ExecuteDataSet(sql, commandType, connection, GetContextTransaction(), parameters);
            }
            finally
            {
                if (creat)
                {
                    connection.Close();
                }
            }
        }

        public DataSet ExecuteDataSet(string sql, IsolationLevel iso)
        {
            return ExecuteDataSet(sql, CommandType.Text, null, iso);
        }

        public DataSet ExecuteDataSet(string sql, CommandType commandType, IsolationLevel iso)
        {
            return ExecuteDataSet(sql, commandType, null, iso);
        }

        public DataSet ExecuteDataSet(string sql, MySqlParameter[] parameters, IsolationLevel iso)
        {
            return ExecuteDataSet(sql, CommandType.Text, parameters, iso);
        }

        public DataSet ExecuteDataSet(string sql, CommandType commandType, MySqlParameter[] parameters, IsolationLevel iso)
        {
            bool creat = false;
            MySqlConnection connection = GetConnection(out creat);
            MySqlTransaction transaction = null;
            bool newTrans = false;
            try
            {
                transaction = BeginTransaction(connection, iso, out newTrans);
                DataSet result = ExecuteDataSet(sql, commandType, connection, transaction, parameters);
                if (newTrans)
                {
                    transaction.Commit();
                }
                return result;
            }
            catch
            {
                if (transaction != null
                    && newTrans)
                {
                    transaction.Rollback();
                }
                throw;
            }
            finally
            {
                if (creat)
                {
                    connection.Close();
                }
            }
        }

        #endregion


        #region ExecuteReaderExtension

        public MySqlDataReaderExtension ExecuteReaderExtension(string sql)
        {
            return ExecuteReaderExtension(sql, CommandType.Text, null);
        }

        public MySqlDataReaderExtension ExecuteReaderExtension(string sql, MySqlParameter[] parameters)
        {
            return ExecuteReaderExtension(sql, CommandType.Text, parameters);
        }

        public MySqlDataReaderExtension ExecuteReaderExtension(string sql, CommandType commandType, MySqlParameter[] parameters)
        {
            bool creat = false;
            MySqlConnection connection = GetConnection(out creat);
            try
            {
                MySqlDataReader reader = MySqlOperation.ExecuteReader(sql, commandType, connection, GetContextTransaction(), parameters);
                MySqlDataReaderExtension result = new MySqlDataReaderExtension(reader);
                if (creat)
                {
                    result.Connection = connection;
                }
                return result;
            }
            catch
            {
                if (creat)
                {
                    connection.Close();
                }
                throw;
            }
        }

        public MySqlDataReaderExtension ExecuteReaderExtension(string sql, IsolationLevel iso)
        {
            return ExecuteReaderExtension(sql, CommandType.Text, null, iso);
        }

        public MySqlDataReaderExtension ExecuteReaderExtension(string sql, MySqlParameter[] parameters, IsolationLevel iso)
        {
            return ExecuteReaderExtension(sql, CommandType.Text, parameters, iso);
        }

        public MySqlDataReaderExtension ExecuteReaderExtension(string sql, CommandType commandType, MySqlParameter[] parameters, IsolationLevel iso)
        {
            bool creat = false;
            MySqlConnection connection = GetConnection(out creat);
            MySqlTransaction transaction = null;
            bool newTrans = false;
            try
            {
                transaction = BeginTransaction(connection, iso, out newTrans);
                MySqlDataReader reader = MySqlOperation.ExecuteReader(sql, commandType, connection, transaction, parameters);
                MySqlDataReaderExtension result = new MySqlDataReaderExtension(reader);
                if (newTrans)
                {
                    result.Transaction = transaction;
                }
                if (creat)
                {
                    result.Connection = connection;
                }
                return result;
            }
            catch
            {
                if (transaction != null
                    && newTrans)
                {
                    transaction.Rollback();
                }
                if (creat)
                {
                    connection.Close();
                }
                throw;
            }
        }

        #endregion


    }
}
